package db;

import entities.*;

import java.sql.*;
import java.util.*;

public class DbRequestManager {

	static Connection con = null;

	static PreparedStatement addRequest, delReq, getReq, getAllReq, delReqCopy, getReqCopy;

	public static final int ALREADY_EXISTS = -2;

	private static void getConnection() throws SQLException {
		if (!Connector.isValidConnection(con)) {
			con = Connector.getConnection();
			// prepare statements
			addRequest = con
					.prepareStatement("Insert into tblrequest(requesterID, ownerID,copyID,requestStatus,timeAdded) values (?, ?, ?, 'pending',now())");
			delReq = con
					.prepareStatement("Delete from tblrequest where requestId=?");
			getReq = con
					.prepareStatement("Select * from tblrequest where requestId=?");
			getAllReq = con
					.prepareStatement("select * from tblrequest left join tbluser on tblrequest.requesterId=tbluser.userId "
							+ "left join tblcopies on tblcopies.copyId=tblrequest.copyID "
							+ " left join tblmasteritem on tblcopies.itemID=tblmasterItem.itemID where tblrequest.ownerID=? order by tblrequest.timeAdded desc");
			delReqCopy = con
					.prepareStatement("delete from tblrequest where copyId = ?");
			getReqCopy = con
					.prepareStatement("select * from tblrequest where copyId = ?");

		}
	}

	public static ArrayList<Integer> addRequest(Integer[] id, int userId,
			Integer[] ownerId) {
		ArrayList<Integer> failedId = new ArrayList<Integer>();
		try {
			getConnection();
			addRequest.clearParameters();
			addRequest.setInt(1, userId);
			for (int i = 0; i < id.length; i++) {
				addRequest.setInt(2, ownerId[i].intValue());
				addRequest.setInt(3, id[i].intValue());
				System.out.println(addRequest);
				int rs = addRequest.executeUpdate();
				if (rs != 1)
					failedId.add(id[i]);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		return failedId;
	}

	public static boolean deleteRequest(int reqId) {
		try {
			getConnection();
			delReq.clearParameters();
			delReq.setInt(1, reqId);
			System.out.println(delReq);
			int rs = delReq.executeUpdate();
			return rs == 1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return false;
		}
	}

	public static Request getCopyRequest(int copyId) {
		try {
			getConnection();
			getReqCopy.clearParameters();
			getReqCopy.setInt(1, copyId);
			if ( getReqCopy.executeQuery() == null ) return new Request();
			return new Request();
		}
		catch (SQLException e) {
			System.out.println(e);
			return null;
		}
	}
	
	public static boolean deleteCopyRequest(int copyId) {
		try {
			getConnection();
			delReqCopy.clearParameters();
			delReqCopy.setInt(1, copyId);
			int rs = delReqCopy.executeUpdate();
			return rs == 1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return false;
		}
	}

	public static Request getRequest(int id) {
		try {
			getConnection();
			getReq.clearParameters();
			getReq.setInt(1, id);
			ResultSet res = getReq.executeQuery();
			Request req = new Request();
			while (res.next()) {
				req.setCopyId(res.getInt("copyId"));
				req.setOwnerId(res.getInt("ownerID"));
				req.setRequestorId(res.getInt("RequesterID"));
				req.setReqStatus(res.getString("requestStatus"));

			}
			return req;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}

	public static ArrayList<Request> getAllRequests(int owner) {
		try {
			getConnection();
			getAllReq.clearParameters();
			getAllReq.setInt(1, owner);
			ResultSet res = getAllReq.executeQuery();
			ArrayList<Request> arr = new ArrayList<Request>();
			while (res.next()) {
				Request req = new Request();
				req.setCopyId(res.getInt("copyId"));
				req.setOwnerId(res.getInt("ownerID"));
				req.setReqId(res.getInt("requestId"));
				req.setRequestorId(res.getInt("RequesterID"));
				req.setReqStatus(res.getString("requestStatus"));
				req.setRequester(res.getString("emailAddress"));
				req.setTitle(res.getString("title"));
				req.setType(res.getString("masterType"));
				req.setTimeAdded(res.getDate("timeAdded"));
				arr.add(req);
			}
			return arr;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}

}